import os
import pandas as pd
import time
import datetime
import xlwings as xw
import numpy as np
# 对低于特定数值的标识红色字体
def low_score_red(s,threshold): #s 表示每个分数, 带参数
    color = 'red' if int(s)<threshold else 'black'
    return f"color:{color}"
# 对高于特定数值的标识红色字体
def high_score_red(s,threshold): #s 表示每个分数, 带参数
    color = 'red' if int(s) >threshold else 'black'
    return f"color:{color}"
#对于安币为整数的加上新开通
def remarks(x):
    if type(x)==int and float(x)!=0:  #np.int 在1.2版本已经被弃用
         return '新开通'
    elif float(x)==0:
        return '零余额'
    else:
        return  ""
def remarks_2(x):
    if int(x)==0:
        return '其他'
    else:
        return ""
##================================================================
#文件路径
file_path=os.getcwd()+"\\file_excel\\"
file_name=os.listdir(file_path)
#将excel转换成DataFrame
DataFrame=pd.read_excel(file_path+file_name[0],header=1,dtype="object")

##----------------------------------------------------------------
#将每个标题X轴坐标变成列表
listX=["姓名","所属门店","所属区域","推广中房源量","新增房源量",\
       "创建超30天房源量","在线主推房源量","已用端口资源量",\
       "安币余额","视频中推广中房源量",\
       "推广中且安选中房源量","推广中且全景中房源量"]
#创建匹配用店长名单，之后用来删除
SManger_txt=open(file_path+'匹配店长用.txt',encoding = "utf-8")
#list店长名单
listSManager=list(SManger_txt)
#用第三行的方法能将list中的'\n'去掉
listSManager=[x.strip() for x in listSManager if x.strip()!='']
SManger_txt.close()
#同上创建租赁人员名单
Leasing_txt=open(file_path+'匹配租赁人员用.txt',encoding = "utf-8")
listLeasing=list(Leasing_txt)
listLeasing=[x.strip() for x in listLeasing if x.strip()!='']
Leasing_txt.close()
#同上创建大区名单
Area_txt=open(file_path+'匹配大区用.txt',encoding = "utf-8")
listArea=list(Area_txt)
listArea=[x.strip() for x in listArea if x.strip()!='']
Area_txt.close()
#同上创建表头
Title_txt=open(file_path+'添加表头用.txt',encoding = "utf-8")
listTitle=list(Title_txt)
listTitle=[x.strip() for x in listTitle if x.strip()!='']
Title_txt.close()
##----------------------------------------------------------------

#将网络端口excel转换成DataFrame
file_NetPort=pd.DataFrame()
#选取需要的列
file_NetPort=DataFrame[listX].copy()
#添加一列
file_NetPort.insert(3,"推广中房源量+主推","", allow_duplicates=False)
#推广中房源量+主推，由两个列相加统计而来
file_NetPort["推广中房源量+主推"]=file_NetPort["推广中房源量"]+file_NetPort["在线主推房源量"]

#替换"公司名称"为空，正则表达式使用regex=True很关键  这里报错
file_NetPort["所属门店"]=file_NetPort["所属门店"].replace('公司名称', '',regex = True)
file_NetPort["所属门店"]=file_NetPort["所属门店"].replace('房产', '',regex = True)
##======================================================================
#去掉不需要统计的店长与租赁人员，姓名是第[0]行，通过~取反，选取不包含list的行
for ma in listSManager:
    file_NetPort=file_NetPort[~file_NetPort["姓名"].isin([ma])]      
for ps in listLeasing:
    file_NetPort=file_NetPort[~file_NetPort["姓名"].isin([ps])]
#只统计需要计算的大区    
file_NetPort=file_NetPort[file_NetPort["所属区域"].isin(listArea)]
###给数据排序    bug
##file_NetPort.sort_values(by=[2,1], inplace=True, ascending=[False,False])

#给数据添加备注，不建议使用注释的lambda函数，下面的方法更好用
'''file_NetPort[13]=np.where(isinstance(i,int),'新开通','无')
file_NetPort["备注"]=file_NetPort["安币余额"].apply(lambda x:remarks(x))'''
file_NetPort["安币余额"]=file_NetPort["安币余额"].astype("object")
file_NetPort["备注"]=file_NetPort["安币余额"].apply(remarks)

'''
在1.08α时修复了这个bug，问题出在函数返回值return为None,后续出现'NoneType'
不能相加的bug,给return加了个空值修复。'''
#去除None的一个方法
'''
#将数据转为numpy.array,再进行相加,np.nanmean()中：nan取值为0且取均值时忽略它
remark_merge=np.nanmean\
(np.array([file_NetPort["备注"],pd_remark["备注"]]),axis=1)
file_NetPort["备注"]=pd.DataFrame(remark_merge)
'''
pd_remark=pd.DataFrame()
pd_remark["备注"]=file_NetPort["推广中房源量+主推"].apply(remarks_2)
file_NetPort["备注"]=file_NetPort["备注"]+pd_remark["备注"]


##======================================================================
#从第四列开始subset=[4]，对threshold赋值38（文件中的需求）并标红，
##.style.applymap()调用low_score_red()颜色传递函数
file_NetPort=file_NetPort.style\
.applymap(low_score_red,threshold=39, subset=["推广中房源量+主推"])\
.applymap(low_score_red,threshold=38, subset=["推广中房源量"])\
.applymap(low_score_red,threshold=1, subset=["新增房源量"])\
.applymap(high_score_red,threshold=15, subset=["创建超30天房源量"])\
.applymap(low_score_red,threshold=20, subset=["视频中推广中房源量"])\
.applymap(low_score_red,threshold=30, subset=["推广中且安选中房源量"])\
.applymap(low_score_red,threshold=20, subset=["推广中且全景中房源量"])\
#获取时间================================================================
now_time = datetime.datetime.now()
yes_time = now_time + datetime.timedelta(days=-1)
yes_time_nyr =yes_time.strftime('%Y.%m.%d')
##构建新表格名===========================================================
save_path=file_path+yes_time_nyr+"网络端口检查.xlsx"
##将file_NetPort写到excel里
file_NetPort.to_excel(save_path,index=False)
def xlwings_Funtion():
    ##visible=False不打开excel只读取
    app = xw.App(visible=False, add_book=False)
    app.display_alerts = False    # 关闭一些提示信息，可以加快运行速度。 默认为 True。
    app.screen_updating = False    # 更新显示工作表的内容。默认为 True。关闭它也可以提升运行速度。
    ##wb = app.books.add()
    ##sht = wb.sheets.active
    wb = app.books.open(save_path)
    sht = wb.sheets[0] #定位Sheet1
    sht.range('A1').value = listTitle   #写入一行数据
    sht.range(1,2).column_width = 13    # 设置第2列 列宽。（1,2）为第1行第2列的单元格
    sht.range(1,3).column_width = 12
    sht.range(1,8).column_width = 10
    sht.range(1,14).column_width = 9
    sht.range(1,4).row_height = 60     # 设置第1行 行高
    sht.range('A1:N1').api.Font.Size = 14          # 设置字体的大小。
    sht.range('A1:N1').api.Font.Bold = True	# 加粗    
    sht.range('A:N').api.HorizontalAlignment = -4108    # -4108 水平居中。 -4131 靠左，-4152 靠右。
    sht.range('A1:C1').api.VerticalAlignment = -4108
    sht.range('H1').api.VerticalAlignment = -4108
    sht.range('N1').api.VerticalAlignment = -4108
    sht.range('D1:G1').api.VerticalAlignment = -4130      # -4108 垂直居中（默认）。 -4160 靠上，-4107 靠下， -4130 自动换行对齐。
    sht.range('I1:M1').api.VerticalAlignment = -4130
    sht.range('E1:M1').color = (255,255,0) #设置背景色
    #添加内部边框
    cell = sht.used_range.last_cell
    rows = cell.row  #获取列数
    columns = cell.column  #获取行数
    Data_Range=('A1'+':'+'N'+str(rows))  #模仿上面用一种取巧的方式构造范围传参
    #7是左部框，8是顶部框，9是底部框，10是右部框
    for i in range(8,11):
        sht.range(Data_Range).api.Borders(i).LineStyle=1  #LineStyle=1是直线
        sht.range(Data_Range).api.Borders(i).Weight=2  #边框粗细是2
    sht.range(Data_Range).api.Borders(11).LineStyle = 1  #11内部垂直边线
    sht.range(Data_Range).api.Borders(12).LineStyle = 1  #12内部水平边线
    wb.save()
    wb.close()
    app.quit()
xlwings_Funtion()
print("完成")
##os.system("pause")
